# employment/crud.py

from sqlalchemy.orm import Session
from sqlalchemy import or_
from typing import Optional, List

from . import models, schemas

class CRUDEmployment:
    def get(self, db: Session, student_id: str) -> Optional[models.Employment]:
        return db.query(models.Employment).filter(models.Employment.student_id == student_id).first()

    def get_by_student(self, db: Session, student_id: str) -> List[models.Employment]:
        employment_records = db.query(models.Employment).filter(models.Employment.student_id == student_id).all()
        # 转换日期字段为字符串
        for record in employment_records:
            if hasattr(record, 'job_search_start') and record.job_search_start:
                record.job_search_start = str(record.job_search_start)
            if hasattr(record, 'offer_date') and record.offer_date:
                record.offer_date = str(record.offer_date)
        return employment_records

    def get_by_class(self, db: Session, class_name: str) -> List[models.Employment]:
        employment_records = db.query(models.Employment).filter(models.Employment.class_name == class_name).all()
        # 转换日期字段为字符串
        for record in employment_records:
            if hasattr(record, 'job_search_start') and record.job_search_start:
                record.job_search_start = str(record.job_search_start)
            if hasattr(record, 'offer_date') and record.offer_date:
                record.offer_date = str(record.offer_date)
        return employment_records

    def get_by_filter(self, db: Session, student_id: Optional[str] = None,
                      company_name: Optional[str] = None, min_salary: Optional[int] = None,
                      max_salary: Optional[int] = None) -> List[models.Employment]:
        query = db.query(models.Employment)
        if student_id:
            query = query.filter(models.Employment.student_id == student_id)
        if company_name:
            query = query.filter(models.Employment.company_name.contains(company_name))
        if min_salary:
            query = query.filter(models.Employment.salary >= min_salary)
        if max_salary:
            query = query.filter(models.Employment.salary <= max_salary)
        employment_records = query.all()
        # 转换日期字段为字符串
        for record in employment_records:
            if hasattr(record, 'job_search_start') and record.job_search_start:
                record.job_search_start = str(record.job_search_start)
            if hasattr(record, 'offer_date') and record.offer_date:
                record.offer_date = str(record.offer_date)
        return employment_records

    def create(self, db: Session, employment_in: schemas.EmploymentCreate) -> models.Employment:
        db_employment = models.Employment(**employment_in.model_dump())
        db.add(db_employment)
        db.commit()
        db.refresh(db_employment)
        return db_employment

    def update(self, db: Session, student_id: str, employment_in: schemas.EmploymentUpdate) -> Optional[models.Employment]:
        # 使用直接更新语句而不是先查询再更新，以避免StaleDataError
        update_data = employment_in.model_dump(exclude_unset=True)
        # 排除主键字段，避免尝试更新主键
        if 'student_id' in update_data:
            del update_data['student_id']
            
        # 执行更新操作
        result = db.query(models.Employment).filter(models.Employment.student_id == student_id).update(
            update_data,
            synchronize_session=False  # 不自动同步会话
        )
        
        db.commit()
        
        # 如果有记录被更新，返回更新后的记录
        if result > 0:
            # 重新查询获取更新后的记录
            updated_employment = db.query(models.Employment).filter(models.Employment.student_id == student_id).first()
            # 转换日期字段为字符串
            if updated_employment:
                if hasattr(updated_employment, 'job_search_start') and updated_employment.job_search_start:
                    updated_employment.job_search_start = str(updated_employment.job_search_start)
                if hasattr(updated_employment, 'offer_date') and updated_employment.offer_date:
                    updated_employment.offer_date = str(updated_employment.offer_date)
            return updated_employment
        return None

    def delete(self, db: Session, student_id: str) -> bool:
        db_employment = self.get(db, student_id)
        if db_employment:
            db.delete(db_employment)
            db.commit()
            return True
        return False

crud_employment = CRUDEmployment()